/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package siafi.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;
import java.util.logging.Logger;

import javax.naming.NamingException;

import org.apache.log4j.Level;

import siafi.bean.Concepto;
import siafi.bean.PersonaCentro;
import siafi.bean.Servicio;
import siafi.bean.Vinculacion;

/**
 *
 * @author dgsi
 */
public class TerceroDAO {
private static Logger log = Logger.getLogger(Logger.GLOBAL_LOGGER_NAME);


public static String findByCodigo(String codigo, String vigencia){
	String encontrado=null;
        log.info("Inicio - findByCodigo()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return encontrado;
		}
		
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select doc_estudiante from rpt_matriculas where codigo = '"+ codigo +"' and semestre = '" + vigencia+"'");
			rs = st.executeQuery( sb.toString());
			while (rs.next()) {
				encontrado = rs.getString(1);
                                break;
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findByCodigo() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findByCodigo()");
        return encontrado;
    }

public static String findByDocumento(String documento, String vigencia){
	String encontrado=null;
        log.info("Inicio - findByDocumento()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return encontrado;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select doc_estudiante from rpt_matriculas where doc_estudiante = '"+ documento +"' and semestre = '" + vigencia +"'");
			rs = st.executeQuery( sb.toString());
			while (rs.next()) {
				encontrado = rs.getString(1);
                                break;
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findByDocumento() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findByDocumento()");
        return encontrado;
    }

public static String findByIdEntidad(String usuario){
	String encontrado=null;
        log.info("Inicio - findByIdEntidad()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return encontrado;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT IDU_ENTIDAD FROM   USUARIOS_ENTIDAD WHERE  USUARIO = '" + usuario +"'");
			rs = st.executeQuery( sb.toString());
			while (rs.next()) {
				encontrado = rs.getString(1);
                                break;
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findByIdEntidad() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findByIdEntidad()");
        return encontrado;
    }

public static Map<String, String> findByDocumentoResumen(String documento){
        log.info("Inicio - findByDocumentoResumen()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		Map<String, String> recibos = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return recibos;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select doc_estudiante, programa, semestre, nro_cuota, idu_documento, val_recibo from rpt_matriculas_resumen where doc_estudiante = '"+ documento +"' order by semestre, nro_cuota");
			rs = st.executeQuery( sb.toString());
			recibos = new TreeMap<String, String>(); 
			while (rs.next()) {
				recibos.put(rs.getString("idu_documento"), rs.getString("programa")+" - Semestre: " +rs.getString("semestre") + " - cuota: "+rs.getFloat("nro_cuota")+ " - valor: "+rs.getFloat("val_recibo"));
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findByDocumentoResumen() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findByDocumentoResumen()");
        return recibos;
    }

public static Map<String, String> findByCodigoResumen(String codigo){
    log.info("Inicio - findByCodigoResumen()");
	Statement st = null;
	ResultSet rs = null;
	Connection con = null;
	Map<String, String> recibos = null;
	try {
		con = ConexionDB.getInstance().getConnection();
	} catch (NamingException e) {
		log.info(e.getMessage());
		for(StackTraceElement elem : e.getStackTrace()) {
			log.info(elem.toString());
		}
		e.printStackTrace();
		return recibos;
	}
	try {
		st = con.createStatement();
		StringBuffer sb = new StringBuffer();
		sb.append("select doc_estudiante, programa, semestre, nro_cuota, idu_documento, val_recibo from rpt_matriculas_resumen where codigo = '"+ codigo +"' order by semestre, nro_cuota");
		rs = st.executeQuery( sb.toString());
		recibos = new TreeMap<String, String>(); 
		while (rs.next()) {
			recibos.put(rs.getString("idu_documento"), rs.getString("programa")+" - Semestre: " +rs.getString("semestre") + " - cuota: "+rs.getFloat("nro_cuota")+ " - valor: "+rs.getFloat("val_recibo"));
		}

	} catch (SQLException sqle) {
		log.info(sqle.getMessage());
		for(StackTraceElement elem : sqle.getStackTrace()) {
			log.info(elem.toString());
		}
		log.info(
				"Error SQL en el metodo findByCodigoResumen() de la clase TerceroDAO: " +
				sqle.getMessage());
	} finally {
		try {
			st.close();
			rs.close();
			con.close();
		} catch (SQLException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
		}
	}
	log.info("Fin - findByCodigoResumen()");
    return recibos;
}

/**
 * Busqueda de personas en la vista personas_centros
 * @param documento
 * @return
 */
public static List <PersonaCentro> findCentroByDocumento(String documento){
	List<PersonaCentro> personaCentros=null;
	PersonaCentro temp = null;
        log.info("Inicio - findCentroByDocumento()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return personaCentros;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select nro_documento, nombre, centro, ide_persona, cod_centro from personas_centros where nro_documento = '"+ documento +"'");
			rs = st.executeQuery( sb.toString());
			personaCentros = new ArrayList<PersonaCentro>();
			while (rs.next()) {
				temp = new PersonaCentro();
				temp.setNro_documento(rs.getString(1));
				temp.setNombre(rs.getString(2));
				temp.setCentro(rs.getString(3));
				temp.setIde_persona(rs.getLong(4));
				temp.setCod_centro(rs.getString(5));
				personaCentros.add(temp);
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findCentroByDocumento() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findCentroByDocumento()");
        return personaCentros;
    }

/**
 * Busqueda de personas en la vista DIRECTORIO
 * @param documento
 * @return
 */
public static PersonaCentro  findByDocumento(String documento){
	PersonaCentro personaCentro=null;
        log.info("Inicio - findByDocumento()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return personaCentro;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT IDE_PERSONA, IND_PARTICULAR, NOMBRE, COD_PERTENENCIA FROM  DIRECTORIO WHERE  NRO_DOCUMENTO = '"+ documento +"'");
			rs = st.executeQuery( sb.toString());
			
			while (rs.next()) {
				personaCentro = new PersonaCentro();
				
				personaCentro.setNro_documento(documento);
				personaCentro.setNombre(rs.getString("NOMBRE"));
				personaCentro.setIde_persona(rs.getLong("IDE_PERSONA"));
				personaCentro.setInd_particular(rs.getString("IND_PARTICULAR"));
				personaCentro.setCod_pertenencia(rs.getString("COD_PERTENENCIA"));
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findByDocumento() de la clase TerceroDAO: " +
					sqle.getMessage());
			personaCentro = null;
		} catch (Exception sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error en el metodo findByDocumento() de la clase TerceroDAO: " +
					sqle.getMessage());
			personaCentro = null;
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findByDocumento()");
        return personaCentro;
    }


/**
 * Busqueda de personas en la vista HOMOLOGA_LISTAS
 * @param documento
 * @return
 */
public static List<Vinculacion>  findVinculacion(String cod_pertenencia){
	Vinculacion vinculacion=null;
	List<Vinculacion> vinculacions = new ArrayList<Vinculacion>();
        log.info("Inicio - findVinculacion()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return vinculacions;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_SUBDOMINIO, SUBDOMINIO FROM  HOMOLOGA_LISTAS WHERE  IDE_HOMOLOGACION  = 40  AND COD_DOMINIO   =  '"+ cod_pertenencia +"'");
			rs = st.executeQuery( sb.toString());
			
			while (rs.next()) {
				vinculacion = new Vinculacion();
				vinculacion.setCod_vinculacion(rs.getString("COD_SUBDOMINIO"));
				vinculacion.setSubdominio(rs.getString("SUBDOMINIO"));
			vinculacions.add(vinculacion);
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findVinculacion() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findVinculacion()");
        return vinculacions;
    }


/**
 * Busqueda de conceptos en la vista CONCEPTOS
 * @param documento
 * @return
 */
public static List<Concepto>  findConceptos(){
	List<Concepto> conceptos=null;
	Concepto concepto = null; 
        log.info("Inicio - findConceptos()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return conceptos;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_CONCEPTO, NOM_CONCEPTO FROM   CONCEPTOS WHERE  COD_NODO IN ( SELECT COD_CONCEPTO FROM   CONCEPTOS_FORMULARIOS WHERE  IDE_DOCUMENTO = 644 ) AND IND_ACTIVA = 'S' ORDER BY COD_CONCEPTO");
			rs = st.executeQuery( sb.toString());
			conceptos = new ArrayList<Concepto>();
			while (rs.next()) {
				concepto = new Concepto();
				
				concepto.setCod_concepto(rs.getString("COD_CONCEPTO"));
				concepto.setNom_concepto(rs.getString("NOM_CONCEPTO"));
				conceptos.add(concepto);
				//System.out.println("concepto: " + concepto.getNom_concepto() + ", " + concepto.getCod_concepto());
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findConceptos() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findConceptos()");
        return conceptos;
    }

/**
 * Busqueda de conceptos en la vista CONCEPTOS
 * @param documento
 * @return
 */
public static Concepto  findConcepto(String id){
	
	Concepto concepto = null; 
        log.info("Inicio - findConcepto()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return concepto;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_CONCEPTO, NOM_CONCEPTO FROM   CONCEPTOS WHERE  COD_CONCEPTO = "+id);
			rs = st.executeQuery( sb.toString());
			
			while (rs.next()) {
				concepto = new Concepto();
				
				concepto.setCod_concepto(rs.getString("COD_CONCEPTO"));
				concepto.setNom_concepto(rs.getString("NOM_CONCEPTO"));
				break;
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findConcepto() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findConcepto()");
        return concepto;
    }


/**
 * Busqueda de centros de costo
 * @param documento
 * @return
 */
public static Map<String, String> findCentrosCosto(){
	Map<String, String> centros=null;
        log.info("Inicio - findCentrosCosto()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return centros;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_CONCEPTO, COD_UNIVERSAL, NOM_CONCEPTO FROM CONCEPTOS WHERE  COD_VERSION = '121' AND IND_ACTIVA = 'S' AND IND_HOJA = 'S' ORDER BY NOM_CONCEPTO");
			rs = st.executeQuery( sb.toString());
			centros = new LinkedHashMap<String, String>();
			while (rs.next()) {
				centros.put(rs.getString(1), rs.getString(2)+"-"+rs.getString(3));
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findCentrosCosto() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findCentrosCosto()");
        return centros;
    }

/**
 * Busqueda de servicios
 * @param documento
 * @return
 */
public static Map<String, String> findServicios(){
	Map<String, String> centros=null;
        log.info("Inicio - findServicios()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return centros;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_CONCEPTO, COD_UNIVERSAL, NOM_CONCEPTO FROM CONCEPTOS WHERE COD_VERSION = '221' AND IND_ACTIVA = 'S' AND IND_HOJA = 'S' AND COD_ATRIBUTO2 = 133007");
			rs = st.executeQuery( sb.toString());
			centros = new LinkedHashMap<String, String>();
			while (rs.next()) {
				centros.put(rs.getString(1), rs.getString(2)+"-"+rs.getString(3));
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findServicios() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findServicios()");
        return centros;
    }

/**
 * Busqueda de servicios
 * @param documento
 * @return
 */
public static List<Servicio> findServicioDetalle(String cod_concepto){
	Servicio servicio=null;
	List<Servicio> servicios = null;
        log.info("Inicio - findServicioDetalle()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return servicios;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_DCONCEPTO, CUN_DCONCEPTO, DCONCEPTO, VALOR FROM   HOMOLOGA_CATALOGOS WHERE  IDE_HOMOLOGACION = 3 AND COD_FCONCEPTO = "+ cod_concepto);
			rs = st.executeQuery( sb.toString());
			servicios = new ArrayList<Servicio>();
			while (rs.next()) {
		//		servicio = new Servicio(rs.getString(1), rs.getString(2), rs.getString(3), Double.valueOf(rs.getDouble(4)).longValue());
				servicios.add(servicio);
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findServicioDetalle() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findServicioDetalle()");
        return servicios;
    }

/**
 * Busqueda de servicios
 * @param documento
 * @return
 */
public static Servicio findServicioDetalle(String cod_concepto, String cun_concepto){
	Servicio servicio=null;
        log.info("Inicio - findServicioDetalle()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return servicio;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT COD_DCONCEPTO, CUN_DCONCEPTO, DCONCEPTO, VALOR FROM   HOMOLOGA_CATALOGOS WHERE  IDE_HOMOLOGACION = 3 AND COD_FCONCEPTO = "+ cod_concepto+" AND CUN_DCONCEPTO = '"+ cun_concepto+"'");
			rs = st.executeQuery( sb.toString());
			while (rs.next()) {
			//	servicio = new Servicio(rs.getString(1), rs.getString(2), rs.getString(3), Double.valueOf(rs.getDouble(4)).longValue());
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findServicioDetalle() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findServicioDetalle()");
        return servicio;
    }

/**
 * Busqueda de servicios
 * @param documento
 * @return
 */
public static List<Servicio> findServicioGrilla(String wCodOperacion, String wCodVinculacion){
	//System.out.println("Cod opercaion: " + wCodOperacion + ", codWinculacion: " + wCodVinculacion);
	List<Servicio> servicios = null;
	Servicio servicio=null;
        log.info("Inicio - findServicioGrilla()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return servicios;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT a.COD_DCONCEPTO COD_CONCEPTO, a.DCONCEPTO CONCEPTO," +
					"d.SIG_DOMINIO UNIDAD,b.VAL_HOMOLOGACION VAL_UNITARIO,1 CAN_LIQUIDACION, ROUND(b.VAL_HOMOLOGACION,0) VAL_SUBTOTAL," +
					"a.IDE_TERCERO IDE_BENEFICIARIO, c.COD_CENTRO FROM HOMOLOGA_CATALOGOS a, " +
					"(SELECT * FROM CONCEPTOS_DOMINIOS WHERE IDE_HOMOLOGACION = 39 AND COD_DOMINIO IN(161100,'"+wCodVinculacion+"'))  b, " +
					"(SELECT COD_DCONCEPTO COD_CONCEPTO, COD_FCONCEPTO COD_CENTRO FROM HOMOLOGA_CATALOGOS " +
					"WHERE IDE_HOMOLOGACION = 38) c, DOMINIOS d WHERE a.IDE_HOMOLOGACION = 3 " +
					"AND a.COD_FCONCEPTO = '"+wCodOperacion+"' AND a.IND_ACTIVA_DES = 'S' AND a.COD_DCONCEPTO = b.COD_CONCEPTO " +
					"AND  a.COD_DCONCEPTO = c.COD_CONCEPTO AND a.COD_ATRIBUTO1 = d.COD_DOMINIO");
			rs = st.executeQuery( sb.toString());
			servicios = new ArrayList<Servicio>();
			while (rs.next()) {
				servicio = new Servicio();
				servicio.setCan_liquidacion(rs.getInt("CAN_LIQUIDACION"));
				servicio.setCod_centro(rs.getString("COD_CENTRO"));
				servicio.setCod_concepto(rs.getString("COD_CONCEPTO"));
				servicio.setConcepto(rs.getString("CONCEPTO"));
				servicio.setIde_beneficiario(rs.getString("IDE_BENEFICIARIO"));
				servicio.setUnidad(rs.getString("UNIDAD"));
				servicio.setVal_subtotal(rs.getLong("VAL_SUBTOTAL"));
				servicio.setValor_unitario(rs.getFloat("VAL_UNITARIO"));
				servicios.add(servicio);
				System.out.println("recibos: " + servicio.getConcepto() + ", " + servicio.getCod_concepto() + ", " + servicio.getCod_centro());
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findServicioGrilla() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findServicioGrilla()");
        return servicios;
    }

/**
 * Busqueda de servicios
 * @param documento
 * @return
 */
public static List<Servicio> findServicioVigentes(String doc_beneficiario){
	List<Servicio> servicios = null;
	Servicio servicio=null;
        log.info("Inicio - findServicioVigentes()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return servicios;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT REFERENCIA, FEC_EXPEDICION, FEC_LIMITE, VAL_RECIBO, IDU_DOCUMENTO FROM RPT_SERVICIOS_RESUMEN" +
					" WHERE DOC_BENEFICIARIO = '"+ doc_beneficiario +"'");
			rs = st.executeQuery(sb.toString());
			servicios = new ArrayList<Servicio>();
			while (rs.next()) {
				servicio = new Servicio();
				servicio.setConcepto(rs.getString("REFERENCIA"));
				servicio.setFec_exp(rs.getDate("FEC_EXPEDICION"));
				servicio.setFec_limite(rs.getDate("FEC_LIMITE"));
				servicio.setVal_subtotal(rs.getLong("VAL_RECIBO"));
				servicio.setIdu_documento(rs.getString("IDU_DOCUMENTO"));
				servicios.add(servicio);
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo findServicioVigentes() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - findServicioVigentes()");
        return servicios;
    }


/**
 * Busqueda de mensaje de error
 * @param documento
 * @return
 */
public static String personanoencontrada(){
	String mensaje=null;
        log.info("Inicio - personanoencontrada()");
		Statement st = null;
		ResultSet rs = null;
		Connection con = null;
		try {
			con = ConexionDB.getInstance().getConnection();
		} catch (NamingException e) {
			log.info(e.getMessage());
			for(StackTraceElement elem : e.getStackTrace()) {
				log.info(elem.toString());
			}
			e.printStackTrace();
			return mensaje;
		}
		try {
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT NOM_MENSAJE  FROM   MENSAJES   WHERE  IDE_MENSAJE = 5129");
			rs = st.executeQuery( sb.toString());
			while (rs.next()) {
				mensaje = rs.getString("NOM_MENSAJE");
				break;
			}

		} catch (SQLException sqle) {
			log.info(sqle.getMessage());
			for(StackTraceElement elem : sqle.getStackTrace()) {
				log.info(elem.toString());
			}
			log.info(
					"Error SQL en el metodo personanoencontrada() de la clase TerceroDAO: " +
					sqle.getMessage());
		} finally {
			try {
				st.close();
				rs.close();
				con.close();
			} catch (SQLException e) {
				log.info(e.getMessage());
				for(StackTraceElement elem : e.getStackTrace()) {
					log.info(elem.toString());
				}
			}
		}
		log.info("Fin - personanoencontrada()");
        return mensaje;
    }

}
